package Dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import OracleHelper.OracleHelper;

import Entity.*;

public class StudentsInformationDao {

	public Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			String user = "c##university_resource";
			String pwd = "20161946";
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;

	}

	// 向studentsInformation表中添加学生信息

	public int addStudent(String studentNo, String name, String sex, String dept, String major, String studentClass,
			String studentId, String pwd, String tel) {
		String sql = "insert into studentsInformation values('" + studentNo + "','" + name + "','" + sex + "','" + dept
				+ "','" + major + "'," + "'" + studentClass + "','" + studentId + "','" + pwd + "','" + tel + "')";
		return new OracleHelper().executeUpdate(sql);

	}

	// 删除学生信息

	public int deleteStudentByNo(String studentNo) {
		String sql = "delete  from studentsInformation where studentNo='" + studentNo + "'";
		return new OracleHelper().executeUpdate(sql);
	}

	// 修改学生密码

	public int alterPassword(String studentNo, String newPwd) {
		String sql = "update studentsInformation set pwd='" + newPwd + "' where studentNo='" + studentNo + "'";

		return new OracleHelper().executeUpdate(sql);

	}

	// 修改学生信息

	public int alterStudentInformation(Student stu) {
		String sql = "update studentsInformation set studentName='" + stu.getStudentName() + "'," + "sex='"
				+ stu.getSex() + "',dept='" + stu.getDept() + "',major='" + stu.getMajor() + "',studentClass='"
				+ stu.getStudentClass() + "'," + "studentId='" + stu.getStudentId() + "',pwd='" + stu.getPwd()
				+ "',tel='" + stu.getTel() + "' where studentNo='" + stu.getStudentNo() + "'";
		return new OracleHelper().executeUpdate(sql);

	}

	// 获取一共有多少条学生信息

	public int queryRecordCount() {
		String sql = "select count(*) from studentsInformation";
		Object obj = new OracleHelper().executeSingleQuery(sql);
		return Integer.parseInt(obj.toString());
	}

	// 通过学号查询某学生所有信息

	public Student queryStudentByNo(String studentNo) {
		Student student = null;
		try {
			String sql = "select * from studentsInformation where studentNo='" + studentNo + "'";
			ResultSet rs = new OracleHelper().executeQuery(sql);
			while (rs.next()) {
				String stuNo = rs.getString(1);
				String stuName = rs.getString(2);
				String sex = rs.getString(3);
				String dept = rs.getString(4);
				String major = rs.getString(5);
				String stuClass = rs.getString(6);
				String stuId = rs.getString(7);
				String pwd = rs.getString(8);
				String tel = rs.getString(9);
				student = new Student(stuNo, stuName, sex, dept, major, stuClass, stuId, pwd, tel);
			}
			OracleHelper.closeConnection();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return student;
	}

	// 显示所有学生信息

	public List<Student> showAllStudents() {
		List<Student> slist = new ArrayList<Student>();
		String sql = "select * from studentsInformation";
		ResultSet rs = new OracleHelper().executeQuery(sql);
		try {
			while (rs.next()) {
				Student student = new Student();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setSex(rs.getString(3));
				student.setDept(rs.getString(4));
				student.setMajor(rs.getString(5));
				student.setStudentClass(rs.getString(6));
				student.setStudentId(rs.getString(7));
				student.setPwd(rs.getString(8));
				student.setTel(rs.getString(9));
				slist.add(student);
			}
			OracleHelper.closeConnection();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return slist;
	}

	/**
	 * 分页显示所有学生的信息
	 */
	public List<Student> showAllStudentsByPage(int page) {
		List<Student> slist = new ArrayList<Student>();
		Connection conn = getConnection();
		String sql = "select studentNo,studentName,sex,dept,major,studentClass,studentId,pwd,tel from(\r\n"
				+ "select studentNo,studentName,sex,dept,major,studentClass,studentId,pwd,tel,rownum as num from studentsInformation)\r\n"
				+ "where num between ? and ?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * Student.PAGE_SIZE + 1);
			ps.setInt(2, page * Student.PAGE_SIZE);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Student stu = new Student();
				stu.setStudentNo(rs.getString(1));
				stu.setStudentName(rs.getString(2));
				stu.setSex(rs.getString(3));
				stu.setDept(rs.getString(4));
				stu.setMajor(rs.getString(5));
				stu.setStudentClass(rs.getString(6));
				stu.setStudentId(rs.getString(7));
				stu.setPwd(rs.getString(8));
				stu.setTel(rs.getString(9));
				slist.add(stu);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return slist;

	}

	/*
	 * public Object[][] showAllStudents() { int nums = queryRecordCount();
	 * Object[][] students = new Object[nums][9]; try { String sql =
	 * "select * from studentsInformation"; ResultSet rs = new
	 * OracleHelper().executeQuery(sql); while (rs.next()) { students[nums][0] =
	 * rs.getString(1); students[nums][1] = rs.getString(2); students[nums][2] =
	 * rs.getString(3); students[nums][3] = rs.getString(4); students[nums][4] =
	 * rs.getString(5); students[nums][5] = rs.getString(6); students[nums][6] =
	 * rs.getString(7); students[nums][7] = rs.getString(8); students[nums][8] =
	 * rs.getString(9); nums++; } OracleHelper.closeConnection(); } catch (Exception
	 * ex) { ex.printStackTrace(); } return students;
	 * 
	 * }
	 */

}
